package service.impl;

import req.CountRequest;
import res.CountDTO;
import service.CountGradeService;
import util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

public class CountGradeServiceImpI implements CountGradeService {

    @Override
    public CountDTO retrieveCount(CountRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT COUNT(*) AS 不及格人数, MIN(grades) AS 最低分, MAX(grades) AS 最高分, AVG(grades) AS 平均分\n" +
                "FROM grade\n" +
                "WHERE grades < 60;\n");
//        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
//            sql.append(" where sno = " + request.getSearchKey().trim());
//        }
        //sql.append("order by sno desc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        PreparedStatement ps = null;
        Connection conn = null;
        ResultSet rs = null;
        CountDTO returnDTO = new CountDTO();
        try {
            conn = DBUtil.getConn();
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查寻记录
            returnDTO.setData(fillData(rs));

//            sql.setLength(0);
//            sql.append("select count(*) from grade ");
//            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
//                sql.append(" where sno = " + request.getSearchKey().trim());
//            }
            //System.out.println(sql);
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }
    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //查寻到的记录
            Vector<Object> oneRecord = new Vector<>();
            int sno = rs.getInt("不及格人数");
            int sname = rs.getInt("最高分");
            String ssex = rs.getString("最低分");
            String sage = rs.getString("平均分");
           // String semester = rs.getString("semester");
//            String sdept = rs.getString("sdept");
//            String sspe = rs.getString("sspe");
//            String sclass = rs.getString("sclass");
            oneRecord.addElement(sno);
            oneRecord.addElement(sname);
            oneRecord.addElement(ssex);
            oneRecord.addElement(sage);
            //oneRecord.addElement(semester);

            System.out.println(sno+" bujige");
            data.addElement(oneRecord);
        }
        return data;
    }
}
